using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Pampa.GenericEntity
{
    public class GenericEntityDBLayer
    {
        SqlConnection conn;
        SqlTransaction transaction;

        public GenericEntityDBLayer()
        {
            NameValueCollection properties = (NameValueCollection)System.Configuration.ConfigurationManager.GetSection("pampa");
            string connectionName = properties["genericentity.connection.name"];

            if(string.IsNullOrEmpty(connectionName))
            {
                throw new Exception("genericentity.connection.name has to be defined");
            }
            else
            {
                string connString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
                conn = new SqlConnection(connString);
            }
        }

        public void Execute(string query, params SqlParameter[] parameters)
        {
            OpenConnection();

            SqlCommand command = new SqlCommand(query, conn);
            command.Transaction = transaction;
 
            foreach (SqlParameter param in parameters)
            {
                command.Parameters.Add(param);
            }
 
            command.ExecuteNonQuery();
        }

        public DataTable ExecuteDataTable(string query)
        {
            OpenConnection();

            SqlCommand command = new SqlCommand(query, conn);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataTable dataTable = new DataTable();
            dataAdapter.Fill(dataTable);
            dataAdapter.Dispose();

            return dataTable;
        }

        public object ExecuteScalar(string query)
        {
            OpenConnection();

            SqlCommand command = new SqlCommand(query, conn);
            command.Transaction = transaction;

            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            return command.ExecuteScalar();
        }

        public void OpenConnection()
        {
            if (this.conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }

        public void CloseConnection()
        {
            if ((this.conn != null) && (this.conn.State == System.Data.ConnectionState.Open))
                this.conn.Close();
        }

        public void BeginTransaction()
        {
            OpenConnection();
            transaction = this.conn.BeginTransaction();
        }

        public void CommitTransaction()
        {
            transaction.Commit();
        }

        public void RollBackTransaction()
        {
            transaction.Rollback();
        }
    }
}
